-- SANH

----- THEM
create proc [dbo].[STOR_THEM_SANH]
@MASANH INT OUT,
@TENSANH nvarchar(50),
@SOLUONGBANTOIDA INT,
@GHICHU NVARCHAR(MAX),
@MALOAISANH INT

as
	begin
		insert into SANH 
	(TENSANH, SOLUONGBANTOIDA, GHICHU, MALOAISANH) VALUES (@TENSANH, @SOLUONGBANTOIDA, @GHICHU, @MALOAISANH)
		set @MASANH = ident_current('SANH')
	end
-------- XOA
CREATE PROC [DBO].[STOR_XOA_SANH]
@MASANH INT
AS
	BEGIN
		DELETE FROM SANH WHERE MASANH=@MASANH
	END

------ SUA
create proc [dbo].[STOR_SUA_SANH]
@MASANH INT OUT,
@TENSANH nvarchar(50),
@SOLUONGBANTOIDA INT,
@GHICHU NVARCHAR(MAX),
@MALOAISANH INT

as
	begin
		UPDATE SANH SET 
	TENSANH=@TENSANH, SOLUONGBANTOIDA=@SOLUONGBANTOIDA, GHICHU=@GHICHU, MALOAISANH=@MALOAISANH WHERE MASANH=@MASANH
		
	end
-- LOAISANH
--------- THEM
create proc [dbo].[STOR_THEM_LOAISANH]
@MALOAISANH INT OUT,
@TENLOAISANH nvarchar(50),
@DONGIATOITHIEU FLOAT,
@SOLUONGBAN INT

as
	begin
		insert into LOAISANH 
	(TENLOAISANH, DONGIATOITHIEU, SOLUONGBAN) VALUES (@TENLOAISANH, @DONGIATOITHIEU, @SOLUONGBAN)
		set @MALOAISANH = ident_current('LOAISANH')
	end
--------- XOA
CREATE PROC [DBO].[STOR_XOA_LOAISANH]
@MALOAISANH INT
AS
	BEGIN
		DELETE FROM LOAISANH WHERE MALOAISANH=@MALOAISANH
	END
-----------SUA
create proc [dbo].[STOR_SUA_LOAISANH]
@MALOAISANH INT,
@TENLOAISANH nvarchar(50),
@DONGIATOITHIEU FLOAT,
@SOLUONGBAN INT

as
	begin
		UPDATE LOAISANH SET
	TENLOAISANH=@TENLOAISANH, DONGIATOITHIEU=@DONGIATOITHIEU, SOLUONGBAN=@SOLUONGBAN WHERE MALOAISANH=@MALOAISANH
		
	end

----------store dang nhap
create proc STR_DangNhap
@TENDANGNHAP NVARCHAR(50),
@MATKHAU NVARCHAR(50)
AS
BEGIN
	select * from nguoidung where TENDANGNHAP=@TENDANGNHAP AND MATKHAU=@MATKHAU
END
EXEC STR_DangNhap N'thanhduy',N'123456'

--------------DS TIEC CUOI 

ALTER proc [dbo].[Xem_DANHSACHTIECCUOI]
as
begin
		
	SELECT TC.MATIECCUOI, TC.TENCHURE,TC.TENCODAU,TC.DIENTHOAI,TC.NGAYCUOI,TC.CA,TC.SOLUONGBANDAT,TC.SOBANDUTRU,TC.TIENDATCOC,
			MA.TENMONAN,MA.LOAIMAMONAN,CTMA.DONGIAMONAN,S.TENSANH,LS.TENLOAISANH,S.SOLUONGBANTOIDA,
			LS.DONGIATOITHIEU,DV.TENDICHVU,CTDV.DONGIADICHVU,S.GHICHU
			
	FROM TIECCUOI TC, CTMONAN CTMA,CTDICHVU CTDV,MONAN MA,SANH S, LOAISANH LS,DICHVU DV
	WHERE	TC.MACHITIETDICHVU=CTDV.MACHITIETDICHVU AND TC.MACHITIETMONAN=CTMA.MACHITIETMONAN AND
			CTDV.MADICHVU=DV.MADICHVU AND CTMA.MAMONAN=MA.MAMONAN AND TC.MASANH=S.MASANH
			AND S.MALOAISANH=LS.MALOAISANH
End